﻿/********************************************************
 * Module Name    : Process
 * Purpose        : synchronise column (update columns information phisically in database)
 * Chronological Development
 * Kiran Sangwan     20-March-2009
  ******************************************************/
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using VAdvantage.DataBase;
using VAdvantage.Classes;
using VAdvantage.Process;
using VAdvantage.Model;
using VAdvantage.Common;
using System.Data;
using System.Data.SqlClient;
using VAdvantage.SqlExec;
using VAdvantage.Utility;

using VAdvantage.ProcessEngine;
namespace VAdvantage.Process
{
   public class ColumnSync : ProcessEngine.SvrProcess
    {
        // The Column				
        private int p_AD_Column_ID = 0;

        /// <summary>
        /// function to et parameters
        /// </summary>
        /// <returns>int</returns>
        /// 
        override protected void Prepare()
        {
            
            ProcessInfoParameter[] para = GetParameter();
            for (int i = 0; i < para.Length; i++)
            {
                String name = para[i].GetParameterName();
               // if (para[i].getParameter() == null)
                {
                }
               // else
                {
                    // log.log(Level.SEVERE, "Unknown Parameter: " + name);
                }
            }
            p_AD_Column_ID = GetRecord_ID();
        }//	prepare


        /// <summary>
        /// Alert table 
        /// </summary>
        /// <returns>int</returns>
        /// 
        override protected string DoIt()
        {
            string exception = "";
            log.Info("C_Column_ID=" + p_AD_Column_ID);
            if (p_AD_Column_ID == 0)
            {
                //    return "";
                throw new Exception("@No@ @AD_Column_ID@");
            }
            //IDbTransaction trx = ExecuteQuery.GerServerTransaction();
            MColumn column = new MColumn(GetCtx(), p_AD_Column_ID, Get_TrxName());
            if (column.Get_ID() == 0)
            {
                throw new Exception("@NotFound@ @AD_Column_ID@" + p_AD_Column_ID);
            }

            MTable table = MTable.Get(GetCtx(), column.GetAD_Table_ID());
            if (table.Get_ID() == 0)
            {
                throw new Exception("@NotFound@ @AD_Table_ID@" + column.GetAD_Table_ID());
            }
            //	Find Column in Database

            DatabaseMetaData md = new DatabaseMetaData();
            String catalog = "";
            String schema = DataBase.DB.GetSchema();

            //get table name
            string tableName = table.GetTableName();

            if(DatabaseType.IsOracle)
                tableName = tableName.ToUpper();

            //if (md.storesUpperCaseIdentifiers())
            //    tableName = tableName.toUpperCase();
            //if (md.storesLowerCaseIdentifiers())
            //    tableName = tableName.toLowerCase();
            int noColumns;
            string sql = null;
            //get columns of a table
            DataSet dt = md.GetColumns(catalog, schema, tableName);

            //for each column
            for (noColumns = 0; noColumns < dt.Tables[0].Rows.Count; noColumns++)
            {
                string columnName = dt.Tables[0].Rows[noColumns]["COLUMN_NAME"].ToString().ToLower();
                if (!columnName.Equals(column.GetColumnName().ToLower()))
                    continue;

                //check if column is null or not

                string dtColumnName = "is_nullable";
                string value = "YES";
                //if database is oracle
                if (DatabaseType.IsOracle)
                {
                    dtColumnName = "NULLABLE";
                    value = "Y";
                }
                bool notNull = false;
                //check if column is null
                if (dt.Tables[0].Rows[noColumns][dtColumnName].ToString() == value)
                    notNull = false;
                else
                    notNull = true;
                //............................

                //if column is virtual column then alter table and drop this column
                if (column.IsVirtualColumn())
                {
                    sql = "ALTER TABLE " + table.GetTableName()
                   + " DROP COLUMN " + columnName;
                }
                else
                {
                    sql = column.GetSQLModify(table, column.IsMandatory() != notNull);
                    noColumns++;
                    break;
                }

            }
            dt = null;

            //while (rs.next())
            //{
            //    noColumns++;
            //    String columnName = rs.getString ("COLUMN_NAME");
            //    if (!columnName.equalsIgnoreCase(column.getColumnName()))
            //        continue;

            //    //	update existing column
            //    boolean notNull = DatabaseMetaData.columnNoNulls == rs.getInt("NULLABLE");
            //    if (column.isVirtualColumn())
            //        sql = "ALTER TABLE " + table.getTableName() 
            //            + " DROP COLUMN " + columnName;
            //    else
            //        sql = column.getSQLModify(table, column.isMandatory() != notNull);
            //    break;
            //}
            //rs.close();
            //rs = null;

            //	No Table
            if (noColumns == 0)
            {
                sql = table.GetSQLCreate();
            }
            //	No existing column
            else if (sql == null)
            {
                if (column.IsVirtualColumn())
                {
                    return "@IsVirtualColumn@";
                } 
                sql = column.GetSQLAdd(table);
            }

            int no = 0;
            if (sql.IndexOf("; ") == -1)
            {
                //no = 
                //ExecuteQuery.ExecuteNonQuery(sql, false, get_TrxName());
                try
                {
                    no = DataBase.DB.ExecuteQuery(sql, null, Get_TrxName());
                    if (DatabaseType.IsPostgre)
                    {
                        if (no == -1)
                            no = 1;
                    }
                    AddLog(0, DateTime.MinValue, Decimal.Parse(no.ToString()), sql);
                }
                catch (Exception ex)
                {
                    exception = ex.Message;
                }
                //addLog (0, null, new BigDecimal(no), sql);
            }
            else
            {
                //string ss = "; ";
                string[] statements = sql.Split(';');
                for (int i = 0; i < statements.Length; i++)
                {
                    int count = DataBase.DB.ExecuteQuery(statements[i].ToString(), null, Get_TrxName());
                    AddLog(0,  DateTime.MinValue, Decimal.Parse(count.ToString()), statements[i]);
                    if (DatabaseType.IsPostgre)
                    {
                        if (no == -1)
                        {
                            no = 1;
                        }
                    }
                    //no += count;
                    //ExecuteQuery.ExecuteNonQuery(statements[i].ToString());
                }
            }

            if (no == -1)
            {
                string msg = "@Error@ ";
                ValueNamePair pp = VAdvantage.Logging.VLogger.RetrieveError();
                if (pp != null)
                    msg += exception + " - ";
                msg += sql;
                throw new Exception(msg);
            }
            return sql;
        }	//	doIt

       
    }
}
